﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Common;
using System.Data.SqlClient;

public class CtrlBDHumanResource
{

    String strConnect;
    String sSql;
    SqlConnection connection;
    SqlCommand command;
    DataSet dataSet = new DataSet();
    SqlDataAdapter adapter = new SqlDataAdapter();

    /// <summary>
    /// Constructor vacío.
    /// </summary>
    public CtrlBDHumanResource() {

        strConnect = ConfigurationManager.ConnectionStrings["conexion"].ConnectionString;
        connection = new SqlConnection(strConnect);

    }

    /// <summary>
    /// Recibe un encapsulado de recurso humano para agregarlo en la base de datos.
    /// </summary>
    /// <param name="human">Encapsulado de recurso humano sin ID para agregar en la base de datos.</param>
    public void addHumanResource(HumanResource human)
    {
        connection.Open();
        sSql = "EXEC addHumanResource @user, @password, @firstName, @lastName, @email, @phone, @profile, @role";
        command = new SqlCommand(sSql, connection);
        SqlParameter p1 = new SqlParameter("@user", human.sUsername);
        SqlParameter p2 = new SqlParameter("@password", human.sPassword);
        SqlParameter p3 = new SqlParameter("@firstName", human.sFirstName);
        SqlParameter p4 = new SqlParameter("@lastName", human.sLastName);
        SqlParameter p5 = new SqlParameter("@email", human.sEmail);
        SqlParameter p6 = new SqlParameter("@phone", human.sPhone);
        SqlParameter p7 = new SqlParameter("@profile", human.iProfile);
        SqlParameter p8 = new SqlParameter("@role", human.iRole);

        command.Parameters.Add(p1);
        command.Parameters.Add(p2);
        command.Parameters.Add(p3);
        command.Parameters.Add(p4);
        command.Parameters.Add(p5);
        command.Parameters.Add(p6);
        command.Parameters.Add(p7);
        command.Parameters.Add(p8);

        command.ExecuteNonQuery();
        connection.Close();
    }

    /// <summary>
    /// Recibe un encapsulado completo de recurso humano para modificarlo en la base de datos.
    /// </summary>
    /// <param name="human">Encapsulado de Recurso Humano.</param>
    public void setHumanResource(HumanResource human)
    {

        connection.Open();
        sSql = "EXEC modifyHumanResource @id, @firstName, @lastName, @email, @phone";
        command = new SqlCommand(sSql, connection);

        SqlParameter p1 = new SqlParameter("@id", human.iId);
        SqlParameter p2 = new SqlParameter("@firstName", human.sFirstName);
        SqlParameter p3 = new SqlParameter("@lastName", human.sLastName);
        SqlParameter p4 = new SqlParameter("@email", human.sEmail);
        SqlParameter p5 = new SqlParameter("@phone", human.sPhone);

        command.Parameters.Add(p1);
        command.Parameters.Add(p2);
        command.Parameters.Add(p3);
        command.Parameters.Add(p4);
        command.Parameters.Add(p5);
        command.ExecuteNonQuery();
        connection.Close();
    }

    /// <summary>
    /// Recibe un entero Id para eliminar de la base de datos.
    /// </summary>
    /// <param name="_iIdProject">Identificador del recurso humano que se desea eliminar.</param>
    public void deleteHumanResource(int iId)
    {
        connection.Open();
        sSql = "EXEC deleteHumanResource @iId";
        command = new SqlCommand(sSql, connection);
        SqlParameter p1 = new SqlParameter("@iId", iId);
        command.Parameters.Add(p1);
        command.ExecuteNonQuery();
        connection.Close();
    }

    /// <summary>
    /// Devuelve un dataset con la consulta hecha, solamente Id y Nombre de todos los recursos humanos.
    /// </summary>
    public DataSet getData()
    {
        dataSet = new DataSet();

        connection.Open();
        sSql = "SELECT Id, First_Name as Name FROM Human_Resource";
        adapter = new SqlDataAdapter(sSql, connection);
        adapter.Fill(dataSet, "Human Resource");
        connection.Close();
        return dataSet;
    }

    public DataSet getDataC(int iId)
    {
        dataSet = new DataSet();
        connection.Open();
        sSql = "EXEC consultHumanResourceC @id";
        command = new SqlCommand(sSql, connection);
        SqlParameter p1 = new SqlParameter("@id", iId);
         command.Parameters.Add(p1);
        adapter = new SqlDataAdapter(command);
        adapter.Fill(dataSet, "Human Resource");
        connection.Close();
        return dataSet;
    }

    /// <summary>
    /// Devuelve un encapsulado del recurso humano que se mandó a consultar.
    /// </summary>
    /// <param name="_iIdProject">Identificador del recurso humano a consultar.</param>
    public HumanResource getHumanResource(int iId)
    {
        DataTable table = new DataTable();
        connection.Open();

        command = new SqlCommand("EXEC consultHumanResource @id", connection);
        SqlParameter p1 = new SqlParameter("@id", iId);
        command.Parameters.Add(p1);
        adapter = new SqlDataAdapter(command);
        adapter.Fill(table);

        HumanResource human = new HumanResource(Int32.Parse(table.Rows[0][0].ToString()),table.Rows[0][1].ToString(), table.Rows[0][2].ToString(),table.Rows[0][3].ToString(), table.Rows[0][4].ToString(),table.Rows[0][5].ToString(),table.Rows[0][6].ToString(), Int32.Parse(table.Rows[0][7].ToString()),Int32.Parse(table.Rows[0][8].ToString()));
        connection.Close();
        return human;
    }
    
}
